package com.informet.ipd.modelo.dao;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */



import com.informet.ipd.modelo.vo.AvancesPorActividadVO;
import com.informet.ipd.modelo.vo.CrecerConDeporteVO;
import com.informet.ipd.modelo.vo.ProgEstrVO;


import com.informet.ipd.modelo.vo.UbigeoVO;

import java.sql.CallableStatement;
import java.sql.Connection; 
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class AlumnoMonitoreoDao extends DBase{


  Connection conn = null;

    public AlumnoMonitoreoDao() {
        super();
    }



////// Ini agregado para las permanencias

      public void  setCausaRetiro(CrecerConDeporteVO bean, ResultSet rs) throws SQLException{

           bean.setCodcausaRet(rs.getString("NUM_CODcausaret_PK"));
           bean.setDescCausaRet(rs.getString("tx_desccausaret"));

    }

    public List getListCausaRetiro(String id,String trimestre)  throws SQLException {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<CrecerConDeporteVO> lista = new ArrayList<CrecerConDeporteVO>(0);

        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();

                sql = "  select * from me_causaret ";


            rs = stmt.executeQuery(sql);
            CrecerConDeporteVO bean = null;
            while (rs.next()) {
                bean = new CrecerConDeporteVO();
                setCausaRetiro(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }
//// ////
    public void  setListAlumno(CrecerConDeporteVO bean, ResultSet rs) throws SQLException{

          bean.setNombre(rs.getString("nombre"));
          bean.setTrimestre1(rs.getString("tx_desctrimestre"));
          bean.setCodAlumno(rs.getString("num_codalumccd_pk"));
          bean.setCodCcd(Integer.parseInt(rs.getString("num_ccd_pk")));
          //bean.setNumEntradaEt(rs.getString("num_entradaet"));
          bean.setTxsDescDisc(rs.getString("tx_descevento"));// se usa para descripcion de evento
          bean.setDescripcionAspectos(rs.getString("tip"))  ;
          bean.setAcumulado(rs.getString("sumaNota"));
          bean.setCodAspTecnico(rs.getString("NUM_CODEVENTOTRIMESTRE_PK"));
          bean.setAnio(rs.getString("num_anioccd"));
          
          bean.setNumCodcausaRet(rs.getString("num_codcausaret_pk"));
          bean.setDescCausaRet(rs.getString("decCausaRetiro"));
          
          /*
              bean.setDescAspTecnico(rs.getString("tx_descasptec"));
              bean.setTrimestre2(rs.getString("num_trim2cet"));
              bean.setTrimestre3(rs.getString("num_trim3cet"));
              bean.setTrimestre4(rs.getString("num_trim4cet"));
              bean.setCodcausaRet(rs.getString("num_codcausaret_pk"));
              bean.setDescPermanencia(rs.getString("num_permaneceet"));

           */


    }


public List getListAlumno(String direccion,String ugel,String anio,String codDisciplina,String profesor,String nivel,
                          String continuidad,String  institucionEducativa,String  supervisor,String  anioInicio) throws SQLException {

        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<CrecerConDeporteVO> lista = new ArrayList<CrecerConDeporteVO>(0);
        //lista=null;
        String sql = "";
        
        try {
            conn = DriverManager.getConnection(url, usuario, password);            
            stmt = conn.createStatement();


                       sql="     select "+
                            " distinct(evt.num_anio)num_anioccd ,   "+
			    " evt.tx_desctrimestre,  "+
                            " et.num_codalumccd_pk,   "+
                            " (a.tx_nombreaccd+' '+ a.tx_apaternoaccd+' '+ a.tx_amaternoaccd) as nombre, "+
                            
			    "	 'Aspecto Tec' as tip,  "+  			    
			    "	 et.num_ccd_pk, "+
		          //  "    et.num_entradaet, "+
			    //"	 --net.num_notaevatec, "+
			    "	 evt.tx_descevento, "+
			    "	isnull((select  "+
			    "			avg(num_notaevatec)  "+
			    "	 from me_notaevatec "+
			    "	 where num_codalumccd_pk=net.num_codalumccd_pk	"+
			    "		   and num_ccd_pk=net.num_ccd_pk "+
			    "	       and NUM_CODEVENTOTRIMESTRE_PK=evt.NUM_CODEVENTOTRIMESTRE_PK "+
			    "	),'') as sumaNota, "+
			    "	 evt.NUM_CODEVENTOTRIMESTRE_PK, "+
                            "	 isnull(net.num_codcausaret_pk,0)as num_codcausaret_pk, "+
                            "	 isnull((select tx_desccausaret from me_causaret where num_codcausaret_pk=isnull(net.num_codcausaret_pk,0)),'') as decCausaRetiro "+
                            " from me_evatec et inner join me_alumnosccd a on et.num_codalumccd_pk=a.num_codalumccd_pk  "+
                            "                           and et.num_ccd_pk=a.num_ccd_pk  "+
                            "               inner join me_asptecnicos at on at.num_codasptec_pk=et.num_codasptec_pk  "+
                            "               inner join me_disciplina d on at.num_coddisc_pk=d.num_coddisc_pk  "+
                            "               inner join me_ccd c on c.num_ccd_pk=a.num_ccd_pk  "+
                            "               inner join me_notaevatec net on net.num_codalumccd_pk=et.num_codalumccd_pk "+
                            "                                                   and net.num_codasptec_pk=at.num_codasptec_pk "+
                            "                                                   and c.num_ccd_pk=net.num_ccd_pk "+
                            "               inner join me_eventotrimestre evt on evt.NUM_CODEVENTOTRIMESTRE_PK=net.NUM_CODEVENTOTRIMESTRE_PK "+
                            "               inner join me_evafis efis on efis.num_ccd_pk=a.num_ccd_pk  and  "+
                            "                          efis.num_codalumccd_pk=a.num_codalumccd_pk "+


                            "  where evt.num_anio= datepart(yy,getdate())  ";

                        if(ugel!=null)
                                if(!ugel.equals("-1"))
                            sql=sql+" and c.num_codugel_pk="+ugel;

                        if(anio!=null)
                                if(!anio.equals("-1"))
                            sql=sql+" and c.num_anioccd= "+anio;

                        if(direccion!=null)
                                if(!direccion.equals(""))
                            sql=sql+" and c.tx_direcccd like '%"+direccion+"%'";

                        if(institucionEducativa!=null)
                                if(!institucionEducativa.equals(""))
                            sql=sql+" and c.tx_instccd like '%"+institucionEducativa+"%'";

                        if(profesor!=null)
                                if(!profesor.equals(""))
                            sql=sql+" and c.tx_profccd  like '%"+profesor+"%'";

                       if(supervisor!=null)
                                if(!supervisor.equals(""))
                            sql=sql+" and c.tx_superccd  like '%"+supervisor+"%'";

                       if(anioInicio!=null)
                                if(!anioInicio.equals("-1"))
                            sql=sql+" and c.num_anioiniccd="+anioInicio;

                       if(codDisciplina!=null)
                                if(!codDisciplina.equals("-1"))
                            sql=sql+" and c.num_coddisc_pk="+codDisciplina;

                       if(nivel!=null)
                                if(!nivel.equals("-1"))
                            sql=sql+" and c.num_nivelccd="+nivel;

                       if(continuidad!=null)
                                if(!continuidad.equals("-1"))
                            sql=sql+" and c.num_continuidad="+continuidad;

                         sql=sql+" union " +

                           " select  "+
                           " distinct(evt.num_anio)num_anioccd ,   "+
                           "  evt.tx_desctrimestre,  "+
                           "  ef.num_codalumccd_pk,    "+
                           " (a.tx_nombreaccd+' '+ a.tx_apaternoaccd+' '+ a.tx_amaternoaccd) as nombre, "+
                           
                           "                      'Aspecto Fis' as tip,   "+
                           "                      ef.num_ccd_pk,  "+
                          //"                      ef.num_entradaef,   "+
                          // "                      --net.num_notaevatec,   "+
                           "                      evt.tx_descevento,  "+
                           "                     isnull((select   "+
                           "                                     avg(num_notaevafis)   "+
                           "                      from me_notaevafis  "+
                           "                      where num_codalumccd_pk=nef.num_codalumccd_pk		"+
                           "                                and num_ccd_pk=nef.num_ccd_pk  "+
                           "                            and NUM_CODEVENTOTRIMESTRE_PK=evt.NUM_CODEVENTOTRIMESTRE_PK  "+
                           "                     ),'') as sumaNota,  "+
                           "                      evt.NUM_CODEVENTOTRIMESTRE_PK,  "+
                           "                    isnull(nef.num_codcausaret_pk,0), "+
                           "	 isnull((select tx_desccausaret from me_causaret where num_codcausaret_pk=isnull(nef.num_codcausaret_pk,0)),'')as decCausaRetiro  "+
                           " from me_evafis ef inner join me_alumnosccd a on ef.num_codalumccd_pk=a.num_codalumccd_pk   "+
                           "                and ef.num_ccd_pk=a.num_ccd_pk   "+
                           "     inner join me_asptecnicos at on at.num_codasptec_pk=ef.num_codaspfis_pf  "+
                           "     inner join me_disciplina d on at.num_coddisc_pk=d.num_coddisc_pk   "+
                           "     inner join me_ccd c on c.num_ccd_pk=a.num_ccd_pk   "+
                           "     inner join me_notaevafis nef on nef.num_codalumccd_pk=ef.num_codalumccd_pk  "+
                           "                                        and nef.num_codaspfis_pf=at.num_codasptec_pk  "+
                           "                                        and c.num_ccd_pk=nef.num_ccd_pk  "+
                           "     inner join me_eventotrimestre evt on evt.NUM_CODEVENTOTRIMESTRE_PK=nef.NUM_CODEVENTOTRIMESTRE_PK  "+
                           "     inner join me_evatec evtec on evtec.num_ccd_pk=a.num_ccd_pk  and  "+
                            "               evtec.num_codalumccd_pk=a.num_codalumccd_pk "+
                           "  where evt.num_anio= datepart(yy,getdate())    ";



                        if(ugel!=null)
                                if(!ugel.equals("-1"))
                            sql=sql+" and c.num_codugel_pk="+ugel;

                        if(anio!=null)
                                if(!anio.equals("-1"))
                            sql=sql+" and c.num_anioccd= "+anio;

                        if(direccion!=null)
                                if(!direccion.equals(""))
                            sql=sql+" and c.tx_direcccd like '%"+direccion+"%'";

                        if(institucionEducativa!=null)
                                if(!institucionEducativa.equals(""))
                            sql=sql+" and c.tx_instccd like '%"+institucionEducativa+"%'";

                        if(profesor!=null)
                                if(!profesor.equals(""))
                            sql=sql+" and c.tx_profccd like '%"+profesor+"%'";

                       if(supervisor!=null)
                                if(!supervisor.equals(""))
                            sql=sql+" and c.tx_superccd like '%"+supervisor+"%'";

                       if(anioInicio!=null)
                                if(!anioInicio.equals("-1"))
                            sql=sql+" and c.num_anioiniccd="+anioInicio;

                       if(codDisciplina!=null)
                                if(!codDisciplina.equals("-1"))
                            sql=sql+" and c.num_coddisc_pk="+codDisciplina;

                       if(nivel!=null)
                                if(!nivel.equals("-1"))
                            sql=sql+" and c.num_nivelccd="+nivel;

                       if(continuidad!=null)
                                if(!continuidad.equals("-1"))
                            sql=sql+" and c.num_continuidad="+continuidad;


                        sql=sql+"  order by num_anioccd,num_codalumccd_pk,num_codeventotrimestre_pk,tx_descevento,tip desc   " ;


                 

            rs = stmt.executeQuery(sql);
            CrecerConDeporteVO bean1 = null;
            while (rs.next()) {
                bean1 = new CrecerConDeporteVO();
                setListAlumno(bean1, rs);
                lista.add(bean1);
            }
           
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
      // JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }
//Ini asiganr evento
public void  setListAlumnoForAsignEvent(CrecerConDeporteVO bean, ResultSet rs) throws SQLException{

          bean.setNombre(rs.getString("nombre"));
          bean.setCodAlumno(rs.getString("num_codalumccd_pk"));
          bean.setCodCcd(Integer.parseInt(rs.getString("num_ccd_pk")));
          bean.setPeso(rs.getString("num_pesoaccd"));
          bean.setTalla(rs.getString("num_tallaaccd"));
    }


public List getListAlumnoForAsignEvent(String idProfesor, String idCodDisciplina ) throws SQLException {

        
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<CrecerConDeporteVO> lista = new ArrayList<CrecerConDeporteVO>(0);
        String sql = "";

        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();


                       sql=" select "+
                          " distinct(a.num_codalumccd_pk)as num_codalumccd_pk, "+
                          " a.num_ccd_pk, "+
                          " a.num_pesoaccd, "+
                          " a.num_tallaaccd, "+
                          " (tx_nombreaccd+' '+ tx_apaternoaccd+' '+ tx_amaternoaccd) as nombre "+
                          " from ME_ALUMNOSCCD a inner join me_evatec e on a.num_codalumccd_pk=e.num_codalumccd_pk and  "+
                          "                                 a.num_ccd_pk=e.num_ccd_pk "+
                          "                      inner join me_evafis ef on a.num_codalumccd_pk=ef.num_codalumccd_pk and  "+
                          "                                 a.num_ccd_pk=ef.num_ccd_pk "+
                          "                      inner join me_ccd c on c.num_ccd_pk=a.num_ccd_pk "+
                          " where 1=1 " ;
                       if(idProfesor!=null)
                            sql=sql+" and num_codProfesor="+idProfesor;
                       if(idCodDisciplina!=null)
                            sql=sql+" and c.num_coddisc_pk="+idCodDisciplina;

                       




            rs = stmt.executeQuery(sql);
            CrecerConDeporteVO bean1 = null;
            while (rs.next()) {
                bean1 = new CrecerConDeporteVO();
                setListAlumnoForAsignEvent(bean1, rs);
                lista.add(bean1);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
      // JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }

    //Fin Asignar Eventos

      public String spMan(CrecerConDeporteVO bean) {
        String msj = "";
        //String codP=bean.getCodPrograma();
        try {

              /*@codAlumno int,@codCcd int,	@codAspTec int,	@tri1 int,@tri2 int,
           @tri3 int,	@tri4 int,@permanece varchar(100),@codCausa int,@tipoAspecto varchar(30)
           */

            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_update_AspectosTecnicosFisicos(?,?,?,?,?,?,?,?,?,?)}");
            //codActividad, codAnio, titu_programa,justificacion,fechaInicio, fechaFin,monto
            
            cs.setString(1, bean.getCodAlumno());
            cs.setInt(2, bean.getCodCcd());
            cs.setString(3, bean.getCodAspTecnico());
            cs.setString(4, bean.getTrimestre1());
            cs.setString(5, bean.getTrimestre2());
            cs.setString(6, bean.getTrimestre3());
            cs.setString(7, bean.getTrimestre4());
            cs.setString(8, bean.getDescPermanencia());
            cs.setString(9, bean.getCodcausaRet());
            cs.setString(10, bean.getDescripcionAspectos());

            
            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

}
